package com.luobo.pcsdepositmanage.util;

import com.alibaba.druid.util.StringUtils;
import org.apache.log4j.Logger;
import org.apache.log4j.spi.LoggerFactory;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Workbook;


/**
 * 提供常用excel操作<br>
 *     <ul>
 *         <li></li>
 *     </ul>
 */
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import java.io.*;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 提供常用excel操作<br>
 *     <ul>
 *         <li></li>
 *     </ul>
 */
public class ExcelOperationUtil {
    private static Logger LOGGER = Logger.getLogger(ExcelOperationUtil.class);
    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";
    private static final DateFormat FORMAT = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");


    /**
     * 从Excel文件获取Workbook对象
     *
     * @param excelFileName Excel文件
     * @return Workbook对象
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static Workbook getWorkbookFromExcel(String excelFileName) throws IOException,NullPointerException {

        File excelFile = new File(excelFileName);
        if (!excelFile.exists()) {
            LOGGER.info(excelFile.toString()+"不存在");
            return new XSSFWorkbook();
        }else{
            try (
                    InputStream inputStream = new FileInputStream(excelFile);
            ) {
                if (excelFile.getName().endsWith(XLS)) {
                    return new HSSFWorkbook(inputStream);
                }

                else if (excelFile.getName().endsWith(XLSX)) {
                    return new XSSFWorkbook(inputStream);
                }

                else {
                    throw new IOException("文件类型错误");
                }
            }
        }

    }

    /**
     * 从Excel文件获取Workbook对象
     *
     * @param excelFile Excel文件
     * @return Workbook对象
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static Workbook getWorkbookFromExcel(File excelFile) throws IOException,NullPointerException {

        if (!excelFile.exists()) {
            LOGGER.info(excelFile.toString()+"不存在");
            return new XSSFWorkbook();
        }else{
            try (
                    InputStream inputStream = new FileInputStream(excelFile);
            ) {
                if (excelFile.getName().endsWith(XLS)) {
                    return new HSSFWorkbook(inputStream);
                }

                else if (excelFile.getName().endsWith(XLSX)) {
                    return new XSSFWorkbook(inputStream);
                }

                else {
                    throw new IOException("文件类型错误");
                }
            }
        }

    }
    /**
     *
     * @param workbook
     * @param map
     * @return
     */
    public static Sheet getSheet(Workbook workbook,Map<String,Object> map){
        Sheet sheet = null;
        if(map.containsKey("sheetname")){
            if(map.get("sheetname") instanceof String){
                String sheetname = (String) map.get("sheetname");
                sheet = workbook.getSheet(sheetname);
                System.out.println("读取sheet页："+sheetname);
            }else if (map.get("sheetname") instanceof Integer){
                int pages = (Integer) map.get("sheetname");
                if(pages < workbook.getNumberOfSheets()){
                    sheet = workbook.getSheetAt(pages);
                    System.out.println("读取sheet页码："+pages);
                }else{
                    System.out.println("不存在"+pages+"页码，增加页面addsheet");
                    sheet = workbook.createSheet("addsheet");
                }
            }else{
                System.out.println("传入的sheetname值："+map.get("sheetname")+"异常！！！，默认选择第0页");
                sheet = workbook.getSheetAt(0);
                System.out.println("读取sheet页码："+0);
            }
        }
        if (sheet == null ){
            System.out.println("0页也没有读取到");
            sheet = workbook.createSheet();
        }
        return sheet ;
    }

    /**
     * 把Workbook对象内容输出到Excel文件
     *
     * @param workbook Workbook对象
     * @param excelFile Excel文件
     * @throws FileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常
     * @throws IOException 输入输出异常
     */
    public static void writeWorkbookToFile(Workbook workbook, File excelFile) throws FileNotFoundException, IOException {

        if (!excelFile.exists()) {
            if (!excelFile.getParentFile().exists()) {
                excelFile.getParentFile().mkdirs();
            }
            excelFile.createNewFile();
        }
        try (
                OutputStream outputStream = new FileOutputStream(excelFile);
        ) {
            writeWorkbookToOutputStream(workbook, outputStream);
        }
    }

    /**
     * 把Workbook对象内容输出到Excel文件
     *
     * @param workbook Workbook对象
     * @param excelFileName Excel文件
     * @throws FileNotFoundException 找不到文件异常，文件已创建，实际不存在该异常
     * @throws IOException 输入输出异常
     */
    public static void writeWorkbookToFile(Workbook workbook, String excelFileName) throws FileNotFoundException, IOException {
        File excelFile = new File(excelFileName);

        if (!excelFile.exists()) {
            if (!excelFile.getParentFile().exists()) {
                excelFile.getParentFile().mkdirs();
            }
            excelFile.createNewFile();
        }
        try (
                OutputStream outputStream = new FileOutputStream(excelFile);
        ) {
            writeWorkbookToOutputStream(workbook, outputStream);
        }
    }

    /**
     * 把Workbook对象输出到Excel输出流
     *
     * @param book Workbook对象
     * @param outputStream Excel输出流
     * @throws IOException 错误时抛出异常，由调用者处理
     */
    public static void writeWorkbookToOutputStream(Workbook book, OutputStream outputStream) throws IOException {
        book.write(outputStream);
    }



    /**
     * 输出数据到Workbook对象中指定页码
     *
     * @param mapList 数据
     * @param map 参数为map，务必包含key： filedir filename sheetname
     * @throws Exception
     */
    public static void writeListMapToWorkbookByMap( List<Map<Integer,Object>> mapList,Map map) throws Exception {

        Workbook workbook = null;
        String filedir = null;
        String filename = null;
        if(map.keySet().contains("filedir")){
            filedir = map.get("filedir").toString();
        }else{
            throw new UnsupportedOperationException("map参数缺少filedir");
        }
        if(map.keySet().contains("filename")){
            filename = map.get("filename").toString();
        }else{
            throw new UnsupportedOperationException("map参数缺少filename");
        }
        if(!map.keySet().contains("sheetname")){
            throw new UnsupportedOperationException("map参数缺少sheetname");
        }


        File exclefile = new File(filedir + "/" + filename);
        workbook = getWorkbookFromExcel(exclefile);

        writeListMapToWorkbook(workbook,mapList,map);

        writeWorkbookToFile(workbook,exclefile);

    }


    public static void writeListMapToWorkbook(Workbook workbook,List<Map<Integer,Object>> listmap,  Map<String,Object> map) {
        Sheet sheet = null;
//        System.out.println(workbook.getNumberOfSheets());
        sheet = getSheet(workbook,map);


        Row row = null;
        Cell cell = null;

        for (int i = 0; i < listmap.size(); i++) {
            row = sheet.getRow(i);
            if (null == row) {
                row = sheet.createRow(i);
            }

            Map<Integer,Object> rowData = null;
            rowData = listmap.get(i);
            if (null == rowData) {
                continue;
            }
            for (int j = 0; j < rowData.size(); j++) {
                cell = row.getCell(j);
                if (null == cell) {
                    cell = row.createCell(j);
                }
                setValue(cell, rowData.get(j));
            }
        }
    }


    /**
     * sheet 复制，复制数据、如果同一个文件，复制样式，不同文件则只复制数据<br/>
     * 如果是同book中复制，建议使用workbook中的cloneSheet()方法<br/>
     *
     * <br/>建议用于 不同book间只复制数据
     *
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet) {
        copySheet(srcSheet, desSheet, true, true, null,null);
    }

    /**
     * sheet 复制，如果同一个文件，复制样式，不同文件则不复制<br/>
     *
     * <br/>建议用于 同book中，只复制样式，不复制数据<br/>
     * eg: copySheet(srcSheet, desSheet, false)
     *
     * @param copyStyleFlag 控制是否复制格式
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyStyleFlag) {
//        copySheet(srcSheet, desSheet, copyValueFlag, true, null);
        copySheet(srcSheet, desSheet, true, copyStyleFlag, null,null);

    }

    /**
     * sheet 复制，复制数据、样式<br/>
     *
     * <br/>建议用于 不同book间复制，同时复制数据和样式<br/>
     * eg: copySheet(srcSheet, desSheet, mapping)
     *
     * @param mapping 不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, StyleMapping mapping) {
        copySheet(srcSheet, desSheet, true, true, mapping,null);
    }

    /**
     * sheet 复制,复制数据<br/>
     *
     *  <br/>建议用于 同book中，只复制数据，不复制样式<br/>
     *  eg: copySheet(srcSheet, desSheet, false, null)
     *
     * @param srcSheet
     * @param desSheet
     * @param copyValueFlag
     * @param mapping
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag, StyleMapping mapping) {
//        copySheet(srcSheet, desSheet, true, copyStyleFlag, mapping);
        copySheet(srcSheet, desSheet, copyValueFlag, true, mapping,null);

    }

    /**
     * sheet 复制, 灵活控制是否控制数据、样式<br/>
     *
     * <br/>不建议直接使用
     *
     * @param copyValueFlag 控制是否复制数据
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping       不同book中复制样式时，必传
     */
    public static void copySheet(Sheet srcSheet, Sheet desSheet, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping,Map<String,Integer> sectionMap) {
        if (srcSheet.getWorkbook() == desSheet.getWorkbook()) {
            LOGGER.warn("统一workbook内复制sheet建议使用 workbook的cloneSheet方法");
        }


        int startRowInt = 0;
        int endRowInt = srcSheet.getPhysicalNumberOfRows();

        if(sectionMap != null && sectionMap.containsKey("startRow") && sectionMap.containsKey("endRow") ){
            startRowInt = sectionMap.get("startRow");
            endRowInt = sectionMap.get("endRow");
        }else if (sectionMap != null && !sectionMap.containsKey("startRow") && sectionMap.containsKey("endRow")  ){
            endRowInt = sectionMap.get("endRow");
        }else if  (sectionMap != null && sectionMap.containsKey("startRow") && !sectionMap.containsKey("endRow")  ){
            startRowInt = sectionMap.get("startRow");

        }

        //合并区域处理
        copyMergedRegion(srcSheet, desSheet,sectionMap);

        //行复制
        Iterator<Row> rowIterator = srcSheet.rowIterator();

        int areadlyColunm = 0;
        while (rowIterator.hasNext()) {
            Row srcRow = rowIterator.next();
            int rowIndex = srcRow.getRowNum();
            if(rowIndex >= startRowInt && rowIndex < endRowInt){
                Row desRow = null;
//                System.out.println(desSheet.getPhysicalNumberOfRows()+"----"+srcRow.getRowNum());
                if(desSheet.getPhysicalNumberOfRows() <= srcRow.getRowNum()){
                    desRow = desSheet.createRow(srcRow.getRowNum());
                }else {
                    desRow = desSheet.getRow(srcRow.getRowNum());
                }
                copyRow(srcRow, desRow, copyValueFlag, copyStyleFlag, mapping,sectionMap);
            }

        }
    }

    /**
     * 复制行，默认复制值，只有同文件复制格式，否则不复制格式。
     */
    public static void copyRow(Row srcRow, Row desRow) {
        copyRow(srcRow, desRow, true, true, null,null);
    }

    /**
     * 复制行，默认复制格式，选择是否复制值，只支持相同文件内复制
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyStyleFlag) {
//        copyRow(srcRow, desRow, copyValueFlag, true, null,null);
        copyRow(srcRow, desRow, true, copyStyleFlag, null,null);

    }

    /**
     * 复制行，默认复制值，复制格式，若为b不用文件需传入mapping
     */
    public static void copyRow(Row srcRow, Row desRow, StyleMapping mapping) {
        copyRow(srcRow, desRow, true, true, mapping,null);
    }

    /**
     * 复制行，默认复制值，选择是否复制格式
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyValueFlag, StyleMapping mapping) {
//        copyRow(srcRow, desRow, true, copyStyleFlag, mapping,null);
        copyRow(srcRow, desRow, true, copyValueFlag, mapping,null);

    }

    /**
     * 复制行
     */
    public static void copyRow(Row srcRow, Row desRow, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping, Map<String,Integer> sectionMap) {
        int startColInt = 0;
        int endColInt = srcRow.getPhysicalNumberOfCells();

        if(sectionMap != null && sectionMap.containsKey("startCol") && sectionMap.containsKey("endCol") ){
            startColInt = sectionMap.get("startCol");
            endColInt = sectionMap.get("endCol");
        }else if (sectionMap != null && !sectionMap.containsKey("startCol") && sectionMap.containsKey("endCol")  ){
            endColInt = sectionMap.get("endCol");
        }else if  (sectionMap != null && sectionMap.containsKey("startCol") && !sectionMap.containsKey("endCol")  ){
            startColInt = sectionMap.get("startCol");

        }

        Iterator<Cell> it = srcRow.cellIterator();
        while (it.hasNext()) {
            Cell srcCell = it.next();
            int srcCellIndex = srcCell.getColumnIndex();
            if(srcCellIndex >= startColInt && srcCellIndex < endColInt){
                Cell desCell = desRow.createCell(srcCellIndex);
                copyCell(srcCell, desCell, copyValueFlag, copyStyleFlag, mapping);
            }else {
                continue;
            }
        }
        copyRowWidth(srcRow,desRow);

    }

    public static void copyRowWidth(Row srcRow, Row desRow){
        //调整行高
        desRow.setHeight(srcRow.getHeight());
    }

    public static void copyColumnWidth(Sheet srcSheet, Sheet desSheet,int rowInt,int colInt){
        //            //调整列宽(增量调整)
        if (srcSheet.getRow(rowInt).getPhysicalNumberOfCells() >= colInt) {
            desSheet.setColumnWidth(colInt, srcSheet.getColumnWidth(colInt));
        }
    }
    /**
     * 复制区域（合并单元格）
     */
    public static void copyMergedRegion(Sheet srcSheet, Sheet desSheet,Map<String,Integer> sectionMap) {

        int startRowInt = 0;
        int endRowInt = srcSheet.getPhysicalNumberOfRows();

        if(sectionMap != null && sectionMap.containsKey("startRow") && sectionMap.containsKey("endRow") ){
            startRowInt = sectionMap.get("startRow");
            endRowInt = sectionMap.get("endRow");
        }else if (sectionMap != null && !sectionMap.containsKey("startRow") && sectionMap.containsKey("endRow")  ){
            endRowInt = sectionMap.get("endRow");
        }else if  (sectionMap != null && sectionMap.containsKey("startRow") && !sectionMap.containsKey("endRow")  ){
            startRowInt = sectionMap.get("startRow");

        }
        int startColInt = 0;
        int endColInt = srcSheet.getRow(endRowInt).getPhysicalNumberOfCells();

        if(sectionMap != null && sectionMap.containsKey("startCol") && sectionMap.containsKey("endCol") ){
            startColInt = sectionMap.get("startCol");
            endColInt = sectionMap.get("endCol");
        }else if (sectionMap != null && !sectionMap.containsKey("startCol") && sectionMap.containsKey("endCol")  ){
            endColInt = sectionMap.get("endCol");
        }else if  (sectionMap != null && sectionMap.containsKey("startCol") && !sectionMap.containsKey("endCol")  ){
            startColInt = sectionMap.get("startCol");

        }

        int sheetMergerCount = srcSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            int firstRow = srcSheet.getMergedRegion(i).getFirstRow();
            int firstCol = srcSheet.getMergedRegion(i).getFirstColumn();
            int lastRow = srcSheet.getMergedRegion(i).getLastRow();
            int lastCol = srcSheet.getMergedRegion(i).getLastColumn();

            if(firstRow >= startRowInt && firstCol >= startColInt && lastRow < endRowInt && lastCol < endColInt){
                desSheet.addMergedRegion(srcSheet.getMergedRegion(i));
                CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i);
            }

        }
    }

    /**
     * 复制单元格，复制数据，如果同文件，复制样式，不同文件则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell) {

        copyCell(srcCell, desCell, true, true,null);
    }

    /**
     * 复制单元格， 如果同文件，复制样式，不同文件则不复制样式
     * @param copyValueFlag 控制是否复制数据
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag) {
        copyCell(srcCell, desCell, copyValueFlag, true, null);
    }

    /**
     * 复制单元格，复制数据,复制样式
     * @param mapping       不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell,  StyleMapping mapping) {
        copyCell(srcCell, desCell, true, true, mapping);
    }

    /**
     * 复制单元格，复制数据
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping       不同文件间复制时，如果要复制样式，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyStyleFlag, StyleMapping mapping) {
        copyCell(srcCell, desCell, true, copyStyleFlag, mapping);
    }

    /**
     * 复制单元格
     * @param copyValueFlag 控制是否复制单元格的内容
     * @param copyStyleFlag 控制是否复制样式
     * @param mapping 不同文件间复制时，如果需要连带样式复制，必传，否则不复制样式
     */
    public static void copyCell(Cell srcCell, Cell desCell, boolean copyValueFlag, boolean copyStyleFlag, StyleMapping mapping) {
        Workbook srcBook = srcCell.getSheet().getWorkbook();
        Workbook desBook = desCell.getSheet().getWorkbook();

        //复制样式
        //如果是同一个excel文件内，连带样式一起复制
        if (srcBook == desBook && copyStyleFlag) {
            //同文件，复制引用
            desCell.setCellStyle(srcCell.getCellStyle());
        } else if (copyStyleFlag) {
            //不同文件，通过映射关系复制
            if (null != mapping) {
                short desIndex = mapping.desIndex(srcCell.getCellStyle().getIndex());
                desCell.setCellStyle(desBook.getCellStyleAt(desIndex));
            }
        }

        //复制评论
        if (srcCell.getCellComment() != null) {
            desCell.setCellComment(srcCell.getCellComment());
        }

        //复制内容
        desCell.setCellType(srcCell.getCellType());

        if (copyValueFlag) {
            switch (srcCell.getCellType()) {
                case STRING:
                    desCell.setCellValue(srcCell.getStringCellValue());
                    break;
                case NUMERIC:
                    desCell.setCellValue(srcCell.getNumericCellValue());
                    break;
                case FORMULA:
                    desCell.setCellFormula(srcCell.getCellFormula());
                    break;
                case BOOLEAN:
                    desCell.setCellValue(srcCell.getBooleanCellValue());
                    break;
                case ERROR:
                    desCell.setCellValue(srcCell.getErrorCellValue());
                    break;
                case BLANK:
                    //nothing to do
                    break;
                default:
                    break;
            }
        }

        copyColumnWidth(srcCell.getSheet(),desCell.getSheet(),srcCell.getRowIndex(),desCell.getColumnIndex());


    }


    /**
     * 把一个excel中的styleTable复制到另一个excel中<br>
     * 如果是同一个excel文件，就不用复制styleTable了
     * @return StyleMapping 两个文件中styleTable的映射关系
     * @see StyleMapping
     */
    public static StyleMapping copyCellStyle(Workbook srcBook, Workbook desBook){
        if (null == srcBook || null == desBook) {
            throw new UnsupportedOperationException("源excel 或 目标excel 不存在");
        }
        if (srcBook.equals(desBook)) {
            throw new UnsupportedOperationException("不要使用此方法在同一个文件中copy style，同一个excel中复制sheet不需要copy Style");
        }
        if ((srcBook instanceof HSSFWorkbook && desBook instanceof XSSFWorkbook) ||
                (srcBook instanceof XSSFWorkbook && desBook instanceof HSSFWorkbook)) {
            throw new UnsupportedOperationException("不支持在不同的版本的excel中复制样式）");
        }

        LOGGER.debug("src中style number:"+srcBook.getNumCellStyles()+", des中style number:"+desBook.getNumCellStyles());
        short[] src2des = new short[srcBook.getNumCellStyles()];
        short[] des2src = new short[desBook.getNumCellStyles() + srcBook.getNumCellStyles()];

        for(short i=0;i<srcBook.getNumCellStyles();i++){
            //建立双向映射
            CellStyle srcStyle = srcBook.getCellStyleAt(i);
            CellStyle desStyle = desBook.createCellStyle();
            src2des[srcStyle.getIndex()] = desStyle.getIndex();
            des2src[desStyle.getIndex()] = srcStyle.getIndex();

            //复制样式
            desStyle.cloneStyleFrom(srcStyle);
        }


        return new StyleMapping(des2src, src2des);
    }

    /**
     * 存放两个excel文件中的styleTable的映射关系，以便于在复制表格时，在目标文件中获取到对应的样式
     */
    public static class StyleMapping {
        /**
         *
         */
        private short[] des2srcIndexMapping;
        /**
         *
         */
        private short[] src2desIndexMapping;

        /**
         * 不允许其他类创建此类型对象
         */
        private StyleMapping() {
        }

        public StyleMapping(short[] des2srcIndexMapping, short[] src2desIndexMapping) {
            this.des2srcIndexMapping = des2srcIndexMapping;
            this.src2desIndexMapping = src2desIndexMapping;
        }

        public short srcIndex(short desIndex) {
            if (desIndex < 0 || desIndex >= this.des2srcIndexMapping.length) {
                throw new UnsupportedOperationException("索引越界：源文件styleNum=" + this.des2srcIndexMapping.length + " 访问位置=" + desIndex);
            }
            return this.des2srcIndexMapping[desIndex];
        }

        /**
         * 根据源文件的style的index,获取目标文件的style的index
         * @param srcIndex 源excel中style的index
         * @return desIndex 目标excel中style的index
         */
        public short desIndex(short srcIndex) {
            if (srcIndex < 0 || srcIndex >= this.src2desIndexMapping.length) {
                throw new UnsupportedOperationException("索引越界：源文件styleNum=" + this.src2desIndexMapping.length + " 访问位置=" + srcIndex);
            }

            return this.src2desIndexMapping[srcIndex];
        }
    }



    public static void setCellForCoordinate(Sheet sheet,int rowIndex,int colIndex,Object value){

        Row row = sheet.getRow(rowIndex);
        if(row == null){
            row.createCell(rowIndex);
        }

        Cell cell = row.getCell(colIndex);
        if(cell == null){
            cell = row.createCell(colIndex);
        }
        setValue(cell,value);
    }

    public static void setCellForIndex(Row row,int colIndex,Object value){
        Cell cell = row.getCell(colIndex);
        if(cell == null){
            cell = row.createCell(colIndex);
        }
        setValue(cell,value);
    }



    /**
     * 从Excle文件中获取List<List<Object>>格式数据
     * @param map  参数为map，务必包含key： filedir filename ，可选包含sheetname ，pages
     * @return
     * @throws Exception
     */
    public static List<Map<Integer,Object>> readExcelFromFileForMapToListMap(Map map) throws Exception {
        Workbook workbook = null;
        List<Map<Integer,Object>> listmap = null;
        String filedir = null;
        String filename = null;
        if(map.keySet().contains("filedir")){
            filedir = map.get("filedir").toString();
        }else{
            throw new UnsupportedOperationException("map参数缺少filedir");
        }
        if(map.keySet().contains("filename")){
            filename = map.get("filename").toString();
        }else{
            throw new UnsupportedOperationException("map参数缺少filename");
        }

        File exclefile = new File(filedir + "/" + filename);

        if (!exclefile.exists()) {
            System.out.println(exclefile.getAbsolutePath());
            throw new FileNotFoundException();
        }else{
            workbook = getWorkbookFromExcel(exclefile);
        }
        LOGGER.info("读取文件：" + filename );
        listmap = readExcelForMapToListMap(workbook,map);
        workbook.close();

        return listmap;
    }


    /**
     * 读取指定页面的Excel
     *
     * @param workbook Workbook对象
     * @param map 页码
     * @return 指定页面数据集合
     */
    public static List<Map<Integer,Object>> readExcelForMapToListMap(Workbook workbook, Map<String,Object> map) {
        List<Map<Integer,Object>> listmap = new ArrayList<>();
        Sheet sheet = getSheet(workbook,map);
        LOGGER.info("读入文件行数："+sheet.getLastRowNum());

        int headrownum = 0;
        if(map.keySet().contains("headrownum")){
            headrownum = (Integer) map.get("headrownum");
        }else{
            throw new UnsupportedOperationException("map参数缺少headrownum");
        }

        for (int i = headrownum; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);

            // 如果当前行为空，则加入空，保持行号一致
            if (null == row) {
                listmap.add(null);
                continue;
            }

            Map<Integer,Object> columnsmap = new HashMap<Integer,Object>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                columnsmap.put(j,getValue(cell));
            }
            listmap.add(columnsmap);

        }

        return listmap;
    }

    /**
     * 解析单元格中的值
     *
     * @param cell 单元格
     * @return 单元格内的值
     */
    public static String getStringValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        DecimalFormat decimalFormat = new DecimalFormat("###################.###########");
        String value = null;
        switch (cell.getCellType()) {
            case BLANK:
                value = "";
                break;
            case FORMULA:
                value = cell.getCellFormula().toString();
                break;
            case BOOLEAN:
                boolean booleanCellValue = cell.getBooleanCellValue();
                value = booleanCellValue ? "TRUE" : "FALSE" ;
                break;
            case STRING:
                String strvalue = cell.getStringCellValue().trim();
                value= StringUtils.isEmpty(strvalue) ? "" : strvalue;
                break;
            case NUMERIC:
                // 日期类型，转换为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue().toString();
                }
                // 数值类型
                else {

                    // 默认返回double，创建BigDecimal返回准确值
//                    value = new BigDecimal(Double.valueOf(cell.getNumericCellValue()).toString());
                    value = decimalFormat.format(Double.valueOf(cell.getNumericCellValue())).toString();
                }
                break;

            default:
                value = cell.toString();
                break;
        }

        return value;
    }

    /**
     * 解析单元格中的值
     *
     * @param cell 单元格
     * @return 单元格内的值
     */
    public static Object getValue(Cell cell) {
        if (null == cell) {
            return null;
        }
        DecimalFormat decimalFormat = new DecimalFormat("###################.###########");
        Object value = null;
        switch (cell.getCellType()) {
            case BLANK:
                value = "";
                break;
            case FORMULA:
                value = cell.getCellFormula();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case STRING:
                String strvalue = cell.getStringCellValue().trim();
                value= StringUtils.isEmpty(strvalue) ? "" : strvalue;
                break;
            case NUMERIC:
                // 日期类型，转换为日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    value = cell.getDateCellValue();
                }
                // 数值类型
                else {

                    // 默认返回double，创建BigDecimal返回准确值
//                    value = new BigDecimal(Double.valueOf(cell.getNumericCellValue()).toString());
                    value = decimalFormat.format(Double.valueOf(cell.getNumericCellValue()));
                }
                break;

            default:
                value = cell.toString();
                break;
        }

        return value;
    }

    /**
     * 设置单元格值
     *
     * @param cell 单元格
     * @param value 值
     */
    public static void setValue(Cell cell, Object value) {
        if (null == cell) {
            return;
        }

        if (null == value) {
            cell.setCellValue((String) null);
        }else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        }else if (value instanceof Date) {
            cell.setCellValue(FORMAT.format((Date) value));
        }else if (value instanceof Double ) {
            cell.setCellValue((Double) value);
        }else if (value instanceof Integer){
            cell.setCellValue(Double.valueOf(value.toString()));
        }else {
            cell.setCellValue(value.toString());
        }

    }
}


